SQL: Logical Operators

AND & OR

What are logical operators?

Logical operators are used to apply multiple criteria that data must meet in order to be retrieved by our query. Criteria for data is placed in the WHERE clause. Logical operators differ from comparison operators in that logical operators are testing wether the criteria is met and not actully defining the criteria.

Some common logical operators include:

Some less common logical operators include:

These logical operators are used in conjunction with subqueries and will be covered in depth later. For now, just now that these less common operators are used in incredibly specific scenarios.

AND & OR

The two operators I want to share today are AND & OR as they are the two most common logical operators in my experience.

AND is used to return data only when both conditions between the AND are met.

OR is used to retuen data when at least one of the conditions are met

There is no limit on how many logical operators that can be used in a WHERE clause. However, it is important to note that the distinctions between how these two operators work are huge. As more AND operators are added, the resulting dataset will be smaller as more constrains are being placed on the data. In contrast, as more OR operators are added, the resulting dataset will grow because the data needs only meet one of the criteria and not all.

SQL Syntax: Using AND & OR

Example:

Customers Table

Customer_ID | First_Name | Last_Name
----------- | -----------|----------
101         | Alice      | Johnson
102         | Bob        | Smith
103         | Logan      | Honey
104         | Tim        | King
105         | John       | Doe
106         | Bob        | Jacobson

Here we have the Customers table just with a few more entried than before.

Normal

All employees who have a first name of Bob.

Query

SELECT Customer_ID
FROM Customers
WHERE First_Name = 'Bob'

Result

Customer_ID
-----------
102 
106

AND

All employees who have a first name of Bob and a last name of Jacobson.

Query

SELECT Customer_ID
FROM Customers
WHERE First_Name = 'Bob' 
        AND Last_Name = 'Jacobson'

Result

Customer_ID
----------- 
106

OR

All employees who have a first name of either Bob or Tim.

Query

SELECT Customer_ID
FROM Customers
WHERE First_Name = 'Bob' 
        OR First_Name = 'Tim'

Result

Customer_ID
-----------
102 
104
106

To Note

AND operators cannot be applied to the same columns. For Example:

Query

SELECT Customer_ID
FROM Customers
WHERE First_Name = 'Bob'
        AND First_Name = 'Alice'

Result

Customer_ID
-----------

The reason that this will not work is because there is no instance where a single row in the First_Name column can be both Alice and Bob simultaneously.

The OR operator can be used since only one of the conditions needs to be true to return a row. For Example:

Query

SELECT Customer_ID
FROM Customers
WHERE First_Name = 'Bob'
        OR First_Name = 'Alice'

Result

Customer_ID
-----------
101
102
106

This returns every Customer_ID for rows in the First_Name column that equal either Bob or Alice.

Wrapping Up

The logical operators AND & OR are the foundations of building complex conditions. After all, a database is only as useful as our ability to find what we seek. AND & OR are fantastic tools to add to you toolbox.